package com.zsr.blob;

import com.zsr.bean.Customer;
import com.zsr.utils.JDBCUtils;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;

public class BlobTest {

    /**
     * 插入Blob数据类型
     */
    @Test
    public void testInsert(){
        try {
            //获取连接
            Connection conn = JDBCUtils.getConn();
            String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);

            // 填充占位符
            ps.setString(1,"许某某");
            ps.setString(2,"xx28@163.com");
            ps.setDate(3,new Date(new java.util.Date().getTime()));
            //操作Blob类型的变量
            FileInputStream fileInputStream = new FileInputStream("src/a.jpg");
            ps.setBlob(4,fileInputStream);

            //执行
            ps.execute();

            fileInputStream.close();
            JDBCUtils.closeResource(conn,ps);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询 Blob数据类型
     */
    @Test
    public void testQuery(){
        try {
            String sql="select id,name,email,birth,photo from customer where id =?";
            Connection conn = JDBCUtils.getConn();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,19);

            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                Integer id = rs.getInt(1);
                String name = rs.getString(2);
                String email = rs.getString(3);
                Date birth = rs.getDate(4);
                Customer cust = new Customer(id, name, email, birth);
                System.out.println(cust);

                //读取Blob类型的字段
                Blob photo = rs.getBlob(5);
                InputStream is = photo.getBinaryStream();
                OutputStream os = new FileOutputStream("c.jpg");
                byte [] buffer = new byte[1024];
                int len = 0;
                while((len = is.read(buffer)) != -1){
                    os.write(buffer, 0, len);
                }

                JDBCUtils.closeResource(conn, ps, rs);
                if(is != null){
                    is.close();
                }
                if(os != null){
                    os.close();
                }


            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 更新Blob数据
     */
    @Test
    public void testUpdate(){
        try {
            Connection conn = JDBCUtils.getConn();
            String sql = "update customers set photo = ? where id = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            // 填充占位符
            // 操作Blob类型的变量
            FileInputStream fis = new FileInputStream("src/b.jpg");
            ps.setBlob(1, fis);
            ps.setInt(2, 19);
            ps.execute();
            fis.close();
            JDBCUtils.closeResource(conn, ps);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
